<?

//Initiate Authenticate
SecurityRedirect ();

//initiate Vars 
define("PRIMARY_KEY","group_id");
$KEY[]         = array("PRIMARY_KEY"=>PRIMARY_KEY);
$FileName      = "ais_tools_gensysmon.php";
$FileTemplate  = "ais_tools_gensysmon_list.html";
$TableName     = "tblgroup";
$SubHeaderTitle= "Generate Sysmon Report";
$PageSize      = 10 ;
$HTMLGridList  = "GroupGrid";

$DBSysmon      = "idms_sysmon";
//************ FORM ACTION **************

//******* DATABASE FORM QUERY ***********

$PeriodYear = GetParam("periodYear","");
$YearDigit = substr($PeriodYear,2,2);
if (GetParam("periodYear","")) { 
$SQL_Patch[] = "
set @ledger='$PeriodYear';
";

$SQL_Patch[] = "
set @perush='050';
";

$SQL_Patch[] = "
CREATE TEMPORARY TABLE `coa_budget` (
  `bgtskada` varchar(8) default NULL,
  `skada_id` int(10) ,
  `ledger` varchar(4)default NULL,
  `perush` char(3) default NULL,
  `ktrasl` char(3) default NULL,
  `skada` varchar(8) default NULL,
  `namacc` varchar(40) default NULL,
  `inc_ppn` tinyint(4) NOT NULL,
  `tipe` char(1) NOT NULL,
  `parent` varchar(8) NOT NULL,
  `grup` char(1) default NULL,
  `skadaold` varchar(8) default NULL,
  `level` varchar(11) NOT NULL default '0',
  `printing` char(1) NOT NULL,
  `jumlah` char(1) NOT NULL,
  `total` char(1) NOT NULL default '0',
  `awal` decimal(15,2) NOT NULL default '0.00',
  `mutd01` decimal(15,2) NOT NULL default '0.00',
  `mutk01` decimal(15,2) NOT NULL default '0.00',
  `aku01` decimal(15,2) NOT NULL default '0.00',
  `mutd02` decimal(15,2) NOT NULL default '0.00',
  `mutk02` decimal(15,2) NOT NULL default '0.00',
  `aku02` decimal(15,2) NOT NULL default '0.00',
  `mutd03` decimal(15,2) NOT NULL default '0.00',
  `mutk03` decimal(15,2) NOT NULL default '0.00',
  `aku03` decimal(15,2) NOT NULL default '0.00',
  `mutd04` decimal(15,2) NOT NULL default '0.00',
  `mutk04` decimal(15,2) NOT NULL default '0.00',
  `aku04` decimal(15,2) NOT NULL default '0.00',
  `mutd05` decimal(15,2) NOT NULL default '0.00',
  `mutk05` decimal(15,2) NOT NULL default '0.00',
  `aku05` decimal(15,2) NOT NULL default '0.00',
  `mutd06` decimal(15,2) NOT NULL default '0.00',
  `mutk06` decimal(15,2) NOT NULL default '0.00',
  `aku06` decimal(15,2) NOT NULL default '0.00',
  `mutd07` decimal(15,2) NOT NULL default '0.00',
  `mutk07` decimal(15,2) NOT NULL default '0.00',
  `aku07` decimal(15,2) NOT NULL default '0.00',
  `mutd08` decimal(15,2) NOT NULL default '0.00',
  `mutk08` decimal(15,2) NOT NULL default '0.00',
  `aku08` decimal(15,2) NOT NULL default '0.00',
  `mutd09` decimal(15,2) NOT NULL default '0.00',
  `mutk09` decimal(15,2) NOT NULL default '0.00',
  `aku09` decimal(15,2) NOT NULL default '0.00',
  `mutd10` decimal(15,2) NOT NULL default '0.00',
  `mutk10` decimal(15,2) NOT NULL default '0.00',
  `aku10` decimal(15,2) NOT NULL default '0.00',
  `mutd11` decimal(15,2) NOT NULL default '0.00',
  `mutk11` decimal(15,2) NOT NULL default '0.00',
  `aku11` decimal(15,2) NOT NULL default '0.00',
  `mutd12` decimal(15,2) NOT NULL default '0.00',
  `mutk12` decimal(15,2) NOT NULL default '0.00',
  `aku12` decimal(15,2) NOT NULL default '0.00',
  `mutd13` decimal(15,2) NOT NULL default '0.00',
  `mutk13` decimal(15,2) NOT NULL default '0.00',
  `aku13` decimal(15,2) NOT NULL default '0.00',
  `akhir` decimal(15,2) NOT NULL default '0.00',
  `bgt01` decimal(15,2) NOT NULL default '0.00',
  `bgt02` decimal(15,2) NOT NULL default '0.00',
  `bgt03` decimal(15,2) NOT NULL default '0.00',
  `bgt04` decimal(15,2) NOT NULL default '0.00',
  `bgt05` decimal(15,2) NOT NULL default '0.00',
  `bgt06` decimal(15,2) NOT NULL default '0.00',
  `bgt07` decimal(15,2) NOT NULL default '0.00',
  `bgt08` decimal(15,2) NOT NULL default '0.00',
  `bgt09` decimal(15,2) NOT NULL default '0.00',
  `bgt10` decimal(15,2) NOT NULL default '0.00',
  `bgt11` decimal(15,2) NOT NULL default '0.00',
  `bgt12` decimal(15,2) NOT NULL default '0.00',
  `bgtd01` decimal(15,2) NOT NULL,
  `bgtd02` decimal(15,2) NOT NULL,
  `bgtd03` decimal(15,2) NOT NULL,
  `bgtd04` decimal(15,2) NOT NULL,
  `bgtd05` decimal(15,2) NOT NULL,
  `bgtd06` decimal(15,2) NOT NULL,
  `bgtd07` decimal(15,2) NOT NULL,
  `bgtd08` decimal(15,2) NOT NULL,
  `bgtd09` decimal(15,2) NOT NULL,
  `bgtd10` decimal(15,2) NOT NULL,
  `bgtd11` decimal(15,2) NOT NULL,
  `bgtd12` decimal(15,2) NOT NULL,
  `pathprn` varchar(255) default NULL,
  `untush` varchar(5) NOT NULL,
  `currency_type` enum('IDR','USD') NOT NULL default 'IDR'
) ENGINE=MyISAM;
";

$SQL_Patch[] = "
INSERT INTO coa_budget 
SELECT bgtskada, tblcoa.* 
FROM tblmapsysmon
INNER JOIN tblcoa ON substring(tblmapsysmon.skada,1,length(tblmapsysmon.skada))=substring(tblcoa.skada,1,length(tblmapsysmon.skada)) AND length(tblcoa.skada)=8 AND tblcoa.ledger=$PeriodYear;
";

$SQL_Patch[] = "
CREATE TEMPORARY TABLE `budget` (
  `PERUSH` varchar(8) NOT NULL default '',
  `KTRASL` varchar(8) NOT NULL default '',
  `SKADA` varchar(8) NOT NULL default '',
  `NAMASKADA` varchar(22) NOT NULL default '',
  `MUT01` decimal(16,2) NOT NULL default '0.00',
  `AKU01` decimal(16,2) NOT NULL default '0.00',
  `MUT02` decimal(16,2) NOT NULL default '0.00',
  `AKU02` decimal(16,2) NOT NULL default '0.00',
  `MUT03` decimal(16,2) NOT NULL default '0.00',
  `AKU03` decimal(16,2) NOT NULL default '0.00',
  `MUT04` decimal(16,2) NOT NULL default '0.00',
  `AKU04` decimal(16,2) NOT NULL default '0.00',
  `MUT05` decimal(16,2) NOT NULL default '0.00',
  `AKU05` decimal(16,2) NOT NULL default '0.00',
  `MUT06` decimal(16,2) NOT NULL default '0.00',
  `AKU06` decimal(16,2) NOT NULL default '0.00',
  `MUT07` decimal(16,2) NOT NULL default '0.00',
  `AKU07` decimal(16,2) NOT NULL default '0.00',
  `MUT08` decimal(16,2) NOT NULL default '0.00',
  `AKU08` decimal(16,2) NOT NULL default '0.00',
  `MUT09` decimal(16,2) NOT NULL default '0.00',
  `AKU09` decimal(16,2) NOT NULL default '0.00',
  `MUT10` decimal(16,2) NOT NULL default '0.00',
  `AKU10` decimal(16,2) NOT NULL default '0.00',
  `MUT11` decimal(16,2) NOT NULL default '0.00',
  `AKU11` decimal(16,2) NOT NULL default '0.00',
  `MUT12` decimal(16,2) NOT NULL default '0.00',
  `AKU12` decimal(16,2) NOT NULL default '0.00',
  `MUT13` decimal(16,2) NOT NULL default '0.00',
  `AKU13` decimal(16,2) NOT NULL default '0.00',
  `AKHIR` decimal(16,2) NOT NULL default '0.00',
  `MUTD01` decimal(16,2) NOT NULL default '0.00',
  `AKUD01` decimal(16,2) NOT NULL default '0.00',
  `MUTD02` decimal(16,2) NOT NULL default '0.00',
  `AKUD02` decimal(16,2) NOT NULL default '0.00',
  `MUTD03` decimal(16,2) NOT NULL default '0.00',
  `AKUD03` decimal(16,2) NOT NULL default '0.00',
  `MUTD04` decimal(16,2) NOT NULL default '0.00',
  `AKUD04` decimal(16,2) NOT NULL default '0.00',
  `MUTD05` decimal(16,2) NOT NULL default '0.00',
  `AKUD05` decimal(16,2) NOT NULL default '0.00',
  `MUTD06` decimal(16,2) NOT NULL default '0.00',
  `AKUD06` decimal(16,2) NOT NULL default '0.00',
  `MUTD07` decimal(16,2) NOT NULL default '0.00',
  `AKUD07` decimal(16,2) NOT NULL default '0.00',
  `MUTD08` decimal(16,2) NOT NULL default '0.00',
  `AKUD08` decimal(16,2) NOT NULL default '0.00',
  `MUTD09` decimal(16,2) NOT NULL default '0.00',
  `AKUD09` decimal(16,2) NOT NULL default '0.00',
  `MUTD10` decimal(16,2) NOT NULL default '0.00',
  `AKUD10` decimal(16,2) NOT NULL default '0.00',
  `MUTD11` decimal(16,2) NOT NULL default '0.00',
  `AKUD11` decimal(16,2) NOT NULL default '0.00',
  `MUTD12` decimal(16,2) NOT NULL default '0.00',
  `AKUD12` decimal(16,2) NOT NULL default '0.00',
  `MUTD13` decimal(16,2) NOT NULL default '0.00',
  `AKUD13` decimal(16,2) NOT NULL default '0.00',
  `AKHIRD` decimal(16,2) NOT NULL default '0.00',
  `KTRLED` varchar(9) NOT NULL default '',
  `FLAG` varchar(9) NOT NULL default '',
  `REMARK` varchar(9) NOT NULL default ''
) ENGINE=MyISAM ;
";

$SQL_Patch[] = "
INSERT INTO budget 
SELECT @perush,
(SELECT ktrasl FROM tblprofile),
coa_budget.bgtskada,
tblsysbgt.namacc, 
sum(bgt01) as mut01,
'0' as aku01,
sum(bgt02) as mut02,
'0' as aku02,
sum(bgt03) as mut03,
'0' as aku03,
sum(bgt04) as mut04,
'0' as aku04,
sum(bgt05) as mut05,
'0' as aku05,
sum(bgt06) as mut06,
'0' as aku06,
sum(bgt07) as mut07,
'0' as aku07,
sum(bgt08) as mut08,
'0' as aku08,
sum(bgt09) as mut09,
'0' as aku09,
sum(bgt10) as mut10,
'0' as aku10,
sum(bgt11) as mut11,
'0' as aku11,
sum(bgt12) as mut12,
'0' as aku12,
'0' as mut13,
'0' as aku13,
'0' as akhir,
sum(bgtd01) as mutd01,
'0' as akud01,
sum(bgtd02) as mutd02,
'0' as akud02,
sum(bgtd03) as mutd03,
'0' as akud03,
sum(bgtd04) as mutd04,
'0' as akud04,
sum(bgtd05) as mutd05,
'0' as akud05,
sum(bgtd06) as mutd06,
'0' as akud06,
sum(bgtd07) as mutd07,
'0' as akud07,
sum(bgtd08) as mutd08,
'0' as akud08,
sum(bgtd09) as mutd09,
'0' as akud09,
sum(bgtd10) as mutd10,
'0' as akud10,
sum(bgtd11) as mutd11,
'0' as akud11,
sum(bgtd12) as mutd12,
'0' as akud12,
'0' as mutd13,                  
'0' as akud13,                          
'0' as akhird,
'' as ktrled,
'' as flag,
'' as remark
FROM coa_budget
INNER JOIN tblsysbgt ON tblsysbgt.skada=coa_budget.bgtskada
GROUP BY coa_budget.bgtskada;
";

$SQL_Patch[] = "
UPDATE budget SET AKU01=MUT01, AKU02=AKU01+MUT02, AKU03=AKU02+MUT03, AKU04=AKU03+MUT04,
									AKU05=AKU04+MUT05, AKU06=AKU05+MUT06, AKU07=AKU06+MUT07,
									AKU08=AKU07+MUT08, AKU09=AKU08+MUT09, AKU10=AKU09+MUT10,
									AKU11=AKU10+MUT11, AKU12=AKU11+MUT12,
									AKUD01=MUTD01, AKUD02=AKUD01+MUTD02, AKUD03=AKUD02+MUTD03, AKUD04=AKUD03+MUTD04, 
									AKUD05=AKUD04+MUTD05, AKUD06=AKUD05+MUTD06, AKUD07=AKUD06+MUTD07, 						 
                  AKUD08=AKUD07+MUTD08, AKUD09=AKUD08+MUTD09, AKUD10=AKUD09+MUTD10,
                  AKUD11=AKUD10+MUTD11, AKUD12=AKUD11+MUTD12; 
";


$SQL_Patch[] = "
UPDATE budget SET AKU01=AKU01/1000, 
									AKU02=AKU02/1000, 
									AKU03=AKU03/1000, 
									AKU04=AKU04/1000,
									AKU05=AKU05/1000, 
									AKU06=AKU06/1000, 
									AKU07=AKU07/1000,
									AKU08=AKU08/1000, 
									AKU09=AKU09/1000, 
									AKU10=AKU10/1000,
									AKU11=AKU11/1000, 
									AKU12=AKU12/1000,
									AKUD01=AKUD01/1000, 
									AKUD02=AKUD02/1000, 
									AKUD03=AKUD03/1000, 
									AKUD04=AKUD04/1000, 
									AKUD05=AKUD05/1000, 
									AKUD06=AKUD06/1000, 
									AKUD07=AKUD07/1000, 						 
                  AKUD08=AKUD08/1000, 
                  AKUD09=AKUD09/1000, 
                  AKUD10=AKUD10/1000,
                  AKUD11=AKUD11/1000, 
                  AKUD12=AKUD12/1000,

                  MUT01=MUT01/1000,  
                  MUT02=MUT02/1000,  
                  MUT03=MUT03/1000,  
                  MUT04=MUT04/1000,  
                  MUT05=MUT05/1000,  
                  MUT06=MUT06/1000,  
                  MUT07=MUT07/1000,  
                  MUT08=MUT08/1000,  
                  MUT09=MUT09/1000,  
                  MUT10=MUT10/1000,  
                  MUT11=MUT11/1000,  
                  MUT12=MUT12/1000,  
                  MUTD01=MUTD01/1000,
                  MUTD02=MUTD02/1000,
                  MUTD03=MUTD03/1000,
                  MUTD04=MUTD04/1000,
                  MUTD05=MUTD05/1000,
                  MUTD06=MUTD06/1000,
                  MUTD07=MUTD07/1000,
                  MUTD08=MUTD08/1000,
                  MUTD09=MUTD09/1000,
                  MUTD10=MUTD10/1000,
                  MUTD11=MUTD11/1000,
                  MUTD12=MUTD12/1000;                  
";                
                  
$SQL_Patch[] = "  
CREATE TABLE IF NOT EXISTS $DBSysmon.RL$YearDigit (
  `LEDGER` varchar(4) NOT NULL default '',
  `PERUSH` char(3) NOT NULL default '',
  `KTRASL` char(3) NOT NULL default '',
  `TGLBKT` date NOT NULL default '0000-00-00',
  `NOBKT` varchar(10) NOT NULL default '',
  `NORMUT` char(3) NOT NULL default '',
  `SKADA` varchar(8) NOT NULL default '',
  `UNTUSH` char(3) NOT NULL default '',
  `CURR` char(2) NOT NULL default '',
  `JUMLAH` decimal(16,2) NOT NULL default '0.00',
  `DK` char(1) NOT NULL default '',
  `RATE` decimal(16,2) NOT NULL default '0.00',
  `RUPIAH` decimal(16,2) NOT NULL default '0.00'
) ENGINE=MyISAM ; 
";                
                  
$SQL_Patch[] = "  
DELETE FROM $DBSysmon.RL$YearDigit WHERE ktrasl=(SELECT ktrasl FROM tblprofile); 
";

$SQL_Patch[] = "
CREATE TEMPORARY TABLE `rskada` (
  `SKADA` varchar(8) NOT NULL default ''
) ENGINE=MyISAM ;
";

$SQL_Patch[] = "
INSERT INTO `rskada` 
SELECT coa_budget.skada FROM coa_budget;
";

$SQL_Patch[] = "
INSERT INTO $DBSysmon.RL$YearDigit
SELECT ledger,@perush,ktrasl,tglbkt,nobkt,urut,ais_memorial.skada,kdact,tucode,jumlah,dk,rate,rupiah
FROM ais_memorial
INNER JOIN tblcurrency ON ais_memorial.curr=tblcurrency.tusymbol
INNER JOIN rskada ON ais_memorial.skada=rskada.skada AND year(ais_memorial.tglbkt)=@ledger;
";

$SQL_Patch[] = "
CREATE TABLE IF NOT EXISTS $DBSysmon.BGTN$YearDigit (
  `PERUSH` varchar(8) NOT NULL default '',
  `KTRASL` varchar(8) NOT NULL default '',
  `SKADA` varchar(8) NOT NULL default '',
  `NAMASKADA` varchar(22) NOT NULL default '',
  `MUT01` decimal(16,2) NOT NULL default '0.00',
  `AKU01` decimal(16,2) NOT NULL default '0.00',
  `MUT02` decimal(16,2) NOT NULL default '0.00',
  `AKU02` decimal(16,2) NOT NULL default '0.00',
  `MUT03` decimal(16,2) NOT NULL default '0.00',
  `AKU03` decimal(16,2) NOT NULL default '0.00',
  `MUT04` decimal(16,2) NOT NULL default '0.00',
  `AKU04` decimal(16,2) NOT NULL default '0.00',
  `MUT05` decimal(16,2) NOT NULL default '0.00',
  `AKU05` decimal(16,2) NOT NULL default '0.00',
  `MUT06` decimal(16,2) NOT NULL default '0.00',
  `AKU06` decimal(16,2) NOT NULL default '0.00',
  `MUT07` decimal(16,2) NOT NULL default '0.00',
  `AKU07` decimal(16,2) NOT NULL default '0.00',
  `MUT08` decimal(16,2) NOT NULL default '0.00',
  `AKU08` decimal(16,2) NOT NULL default '0.00',
  `MUT09` decimal(16,2) NOT NULL default '0.00',
  `AKU09` decimal(16,2) NOT NULL default '0.00',
  `MUT10` decimal(16,2) NOT NULL default '0.00',
  `AKU10` decimal(16,2) NOT NULL default '0.00',
  `MUT11` decimal(16,2) NOT NULL default '0.00',
  `AKU11` decimal(16,2) NOT NULL default '0.00',
  `MUT12` decimal(16,2) NOT NULL default '0.00',
  `AKU12` decimal(16,2) NOT NULL default '0.00',
  `MUT13` decimal(16,2) NOT NULL default '0.00',
  `AKU13` decimal(16,2) NOT NULL default '0.00',
  `AKHIR` decimal(16,2) NOT NULL default '0.00',
  `MUTD01` decimal(16,2) NOT NULL default '0.00',
  `AKUD01` decimal(16,2) NOT NULL default '0.00',
  `MUTD02` decimal(16,2) NOT NULL default '0.00',
  `AKUD02` decimal(16,2) NOT NULL default '0.00',
  `MUTD03` decimal(16,2) NOT NULL default '0.00',
  `AKUD03` decimal(16,2) NOT NULL default '0.00',
  `MUTD04` decimal(16,2) NOT NULL default '0.00',
  `AKUD04` decimal(16,2) NOT NULL default '0.00',
  `MUTD05` decimal(16,2) NOT NULL default '0.00',
  `AKUD05` decimal(16,2) NOT NULL default '0.00',
  `MUTD06` decimal(16,2) NOT NULL default '0.00',
  `AKUD06` decimal(16,2) NOT NULL default '0.00',
  `MUTD07` decimal(16,2) NOT NULL default '0.00',
  `AKUD07` decimal(16,2) NOT NULL default '0.00',
  `MUTD08` decimal(16,2) NOT NULL default '0.00',
  `AKUD08` decimal(16,2) NOT NULL default '0.00',
  `MUTD09` decimal(16,2) NOT NULL default '0.00',
  `AKUD09` decimal(16,2) NOT NULL default '0.00',
  `MUTD10` decimal(16,2) NOT NULL default '0.00',
  `AKUD10` decimal(16,2) NOT NULL default '0.00',
  `MUTD11` decimal(16,2) NOT NULL default '0.00',
  `AKUD11` decimal(16,2) NOT NULL default '0.00',
  `MUTD12` decimal(16,2) NOT NULL default '0.00',
  `AKUD12` decimal(16,2) NOT NULL default '0.00',
  `MUTD13` decimal(16,2) NOT NULL default '0.00',
  `AKUD13` decimal(16,2) NOT NULL default '0.00',
  `AKHIRD` decimal(16,2) NOT NULL default '0.00',
  `KTRLED` varchar(9) NOT NULL default '',
  `FLAG` varchar(9) NOT NULL default '',
  `REMARK` varchar(9) NOT NULL default ''
) ENGINE=MyISAM ;
";

$SQL_Patch[] = "
DELETE FROM $DBSysmon.BGTN$YearDigit WHERE ktrasl=(SELECT ktrasl FROM tblprofile); 
";

$SQL_Patch[] = "
INSERT INTO $DBSysmon.BGTN$YearDigit
SELECT * FROM budget;
";

QueryMulti($SQL_Patch,$DBConnection);

$AlertSucess = "
	<script>alert('Generate Budget Data For Year $PeriodYear Success!');</script>
	";
	print $AlertSucess;
}

$TBS = new clsTinyButStrong ;
$TBS->LoadTemplate($MOD_TEMPLATE_DIR."/".$FileTemplate);
$TBS->MergeBlock("yearformblk",GetArrayYear());
$SQLOffice = "SELECT * FROM tblkantor";
$TBS->MergeBlock("officeformblk",$DBMysql,$SQLOffice);

$TBS->Show();

function QueryMulti($SQL,$DBConnection) {
	for($x=0;$x<count($SQL);$x++)	{
//		print $SQL[$x]."<p>\n";
		$DBConnection->dbc->query($SQL[$x]); 		
	}
}

?>
